package com.api.gbhg.luoao.utils;

import cn.hutool.core.util.StrUtil;

import com.api.gbhg.liwenjun.VersionFlowUtil;
import com.api.gbhg.luoao.tool.ToolCURD;
import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.general.BaseBean;
import com.google.common.base.Joiner;
import org.apache.commons.lang3.StringUtils;
import weaver.conn.RecordSet;


import java.util.*;

public class ReimbursementDao  extends BaseBean {

    private static final Logger logger = LoggerFactory.getLogger("gbhg");

    /**
     * 查询主表数据
     * @param tablefname 表名
     * @param requestId 流程id
     * @param fieldname 经办人字段名
     * @return
     */
    public static RecordSet getPrimaytable(String tablefname, String requestId,String fieldname,String jbrname){
        StringBuffer sb = new StringBuffer();
//        sb.append("select * from "+tablefname+" m,hrmResource e  where" +
//                "  m.requestId in("+requestId+") and m."+fieldname+" = e.id ");
        sb.append("select * from "+tablefname+" m left join hrmResource e on m."+fieldname+" = e.id  left join workflow_requestbase w on w.requestid=m.requestid  where" +
                "  m.requestId in("+requestId+") ");

        if (StrUtil.isNotEmpty(jbrname)){
            sb.append(" and  e.lastname like '%"+jbrname+"%'");
        }

        RecordSet rs = new RecordSet();
        rs.executeQuery(sb.toString());
        return rs;
    }

    //获取明细表1
    public static RecordSet getDetail1(String tablefname,String mainid){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select * from "+tablefname+"_dt1 where mainid ="+mainid);
        return rs;
    }

    //获取明细表2
    public static RecordSet getDetail2(String tablefname,String mainid){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select * from "+tablefname+"_dt2 where mainid ="+mainid);
        return rs;
    }


    //查询部门经济支出分类
    public static String getbmjjczfl(String kdm){
        if (StrUtil.isNotEmpty(kdm)){
            RecordSet rs = new RecordSet();
            rs.executeQuery("select kmc  from  uf_jjzcflkmk where kdm ="+kdm);
            rs.next();
            return  rs.getStringNoTrim("kmc");
        }
        return  " ";

    }


    //查询项目名
    public static String getProjectname(String request){
        if (StrUtil.isNotEmpty(request)){
            RecordSet rs = new RecordSet();
            rs.executeQuery(" select xmmc from uf_jingfei where requestId = "+request+"  group by xmmc");
            rs.next();

            return   rs.getStringNoTrim("xmmc");
        }

        return  "";
    }


    //关联号
    public static String number(String ybzysxm){
        if (StrUtil.isNotEmpty(ybzysxm)){
            RecordSet rs = new RecordSet();
            rs.executeQuery("select glh from  uf_xmglhgl where ybzysxm ="+ybzysxm);
            rs.next();
            return  rs.getStringNoTrim("glh");
        }
      return "";
    }


    public static int  getType(String id,List workflowidlist){


        int type =0;
        if (id.equals(workflowidlist.get(0))){
            return type=1;
        }else if (id.equals(workflowidlist.get(1))){
            return type=2;
        }else if (id.equals(workflowidlist.get(2))){
            return type=3;
        }else if (id.equals(workflowidlist.get(3))){
            return type=4;
        }else if (id.equals(workflowidlist.get(4))){
            return type=5;
        }else if (id.equals(workflowidlist.get(5))){
            return type=6;
        }else if (id.equals(workflowidlist.get(6))){
            return type=7;
        }else if (id.equals(workflowidlist.get(7))){
            return type=8;
        }else if (id.equals(workflowidlist.get(8))){
            return type=9;
        }else if (id.equals(workflowidlist.get(9))){
            return type=10;
        }else if (id.equals(workflowidlist.get(10))){
            return type=11;
        }else if (id.equals(workflowidlist.get(11))){
            return type=12;
        }else if (id.equals(workflowidlist.get(12))){
            return type=13;
        }else if (id.equals(workflowidlist.get(13))){
            return type=18;
        }else if (id.equals(workflowidlist.get(14))){
            return type=19;
        }else if (id.equals(workflowidlist.get(15))){
            return type=20;
        }else if (id.equals(workflowidlist.get(16))){
            return type=21;
        }else if (id.equals(workflowidlist.get(17))){
            return type=22;
        }else if (id.equals(workflowidlist.get(18))){
            return type=23;//借款
        } else if (id.equals(workflowidlist.get(19))){
            return type=24;//借款
        }
        return type;

    }


    //查询表名
    public static RecordSet tablefname(String requestId){
        RecordSet rs = new RecordSet();
        rs.executeQuery("select  l.tablename,s.id,s.formid from workflow_requestbase r,workflow_base s,workflow_bill l where\n" +
                "r.workflowid = s.id and s.formid = l.id and  r.requestid ="+requestId);
        return rs;
    }




    //根据requestid查询该流程数据
    public static  Map<String,Object> workflowinfo(String requestId,String jbrname,String requestname,List<String> workflowidlist){

        //主表表名
        RecordSet recordSet =tablefname(requestId) ;
        recordSet.next();
        String tablename = recordSet.getStringNoTrim("tablename");
        String id =  recordSet.getStringNoTrim("id");
        String formid = recordSet.getStringNoTrim("formid");

        //根据不同的表更改字段名
            String fieldname = "jbr";
            if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(6)))){
                fieldname = "jbry";
            }

            String money = "sqbxjehj";
            //YS15 奖励金领取
            if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(11)))){
                money = "jlje";
            //YS14 公务出国（境）费用报销
            } else if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(10)))){
                money = "bxwbjehj";
            //YS10 缉私举报奖金报销,YS06 培训费报销
            }else if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(6)))||
                    tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(3)))){
                money ="bxje";
             //所有的人员经费
            } else if(id.equals(workflowidlist.get(13))||id.equals(workflowidlist.get(14))
                    ||id.equals(workflowidlist.get(15))||id.equals(workflowidlist.get(16))
                    ||id.equals(workflowidlist.get(17))){
                money = "sqbxhj";
            }else if(tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(18)))){
                money = "sqbxje";
            }

            String yslyname = "ysly";//预算来源
            String bmjjzcflname = "bmjjzcfl";//部门经济分类



            Map<String,Object> maps = new HashMap<>();
            //查询流程主表数据
            RecordSet primaytable = ReimbursementDao.getPrimaytable(
                    tablename,requestId,fieldname,jbrname);
            //查询主表数据
            while (primaytable.next()){

                int type = ReimbursementDao.getType(id,workflowidlist);
                maps.put("type",type);//操作类型
                maps.put("requestId",primaytable.getStringNoTrim("requestId"));
                maps.put("bt",requestname);//标题
                maps.put("jbr",primaytable.getStringNoTrim("lastname"));//经办人
                maps.put("lczt", ToolCURD.Processnode(primaytable.getStringNoTrim("requestId")));//审核状态
                maps.put("sqrq",primaytable.getStringNoTrim("sqrq"));//申请日期
                maps.put("sqbxjehj",primaytable.getStringNoTrim(money));//贷方金额
                maps.put("lcbh",primaytable.getStringNoTrim("lcbh"));//流程编号
                maps.put("currentnodetype",primaytable.getStringNoTrim("currentnodetype"));//当前节点
                maps.put("workflowid",primaytable.getStringNoTrim("workflowid"));
                maps.put("lastoperator",primaytable.getStringNoTrim("lastoperator"));
                maps.put("currentnodeid",primaytable.getStringNoTrim("currentnodeid"));
                maps.put("lastoperatedate",primaytable.getStringNoTrim("lastoperatedate"));
                maps.put("lastoperatetime",primaytable.getStringNoTrim("lastoperatetime"));
                maps.put("formid",formid);

                //YS10 缉私举报奖金报销
                if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(6)))){


                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    List glh = new ArrayList();//关联号
                    Set ysly = new HashSet();//预算来源
                    String ly = primaytable.getStringNoTrim("ysly");
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestId,"ysly",ly));
                    }

                    String xm = primaytable.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm) );
                    }

                    String number = ReimbursementDao.number(xm);//关联号
                    if (StrUtil.isNotEmpty(number)){
                        glh.add(ReimbursementDao.number(xm));
                    }


                    String fl=primaytable.getStringNoTrim(bmjjzcflname);
                    if (StrUtil.isNotEmpty(fl)){
                        bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                    }
                    maps.put("ysly",  StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("bmjjzcfl",Joiner.on(",").join((Iterable<?>) bmjjzcfl));

                    List skr = new ArrayList();//收款人
                    skr.add(primaytable.getStringNoTrim("skr"));

                    maps.put("skr",Joiner.on(",").join((Iterable<?>) skr));
                }

                // YS06 培训费报销
                if (tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(3)))){

                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    Set ysly = new HashSet();//预算来源
                    List glh = new ArrayList();//关联号
                    String ly = primaytable.getStringNoTrim("ysly");
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestId,"ysly",ly));
                    }

                    String xm = primaytable.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm));

                    }
                    String number = ReimbursementDao.number(xm);//关联号
                    if (StrUtil.isNotEmpty(number)){
                        glh.add(ReimbursementDao.number(xm));
                    }

                    String fl=primaytable.getStringNoTrim(bmjjzcflname);
                    if (StrUtil.isNotEmpty(fl)){
                        bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                    }



                    maps.put("bmjjzcfl",Joiner.on(",").join((Iterable<?>) bmjjzcfl));
                    maps.put("ysly", StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("glh",Joiner.on(",").join((Iterable<?>) glh));

                }

                //主表id
                String mainid = primaytable.getStringNoTrim("id");

                //不是流程 YS10 缉私举报奖金报销  进行查询
                if (!tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(6))) && !tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(3)))
                        &&!tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(18)))&&!tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(19)))
                ){

                    //查询明细表1
                    RecordSet Detail1 = ReimbursementDao.getDetail1(tablename, mainid);

                    Set ysly = new HashSet();//预算来源
                    List ysxm = new ArrayList();//预算项目
                    List bmjjzcfl = new ArrayList();//部门经济支出分类
                    List glh = new ArrayList();//关联号
                    while (Detail1.next()){

                    String ly = Detail1.getStringNoTrim(yslyname);
                    if (StrUtil.isNotEmpty(ly)){
                        ysly.add(information.getselectname(requestId,yslyname,ly));
                    }

                    String xm = Detail1.getStringNoTrim("ysxm");
                    if (StrUtil.isNotEmpty(xm)){
                        ysxm.add(ReimbursementDao.getProjectname(xm));

                    }

                    String number = ReimbursementDao.number(xm);//关联号
                    if (StrUtil.isNotEmpty(number)){
                        glh.add(ReimbursementDao.number(xm));
                    }
                    String fl=Detail1.getStringNoTrim(bmjjzcflname);
                    if (StrUtil.isNotEmpty(fl)){
                        bmjjzcfl.add(ReimbursementDao.getbmjjczfl(fl));
                    }

                    }

                    maps.put("bmjjzcfl", Joiner.on(",").join((Iterable<?>) bmjjzcfl));
                    maps.put("ysly", StringUtils.join(ysly.toArray(), ","));
                    maps.put("ysxm", Joiner.on(",").join((Iterable<?>) ysxm));
                    maps.put("glh",Joiner.on(",").join((Iterable<?>) glh));
                }

                //不是流程 YS10 缉私举报奖金报销 进行查询
                if (!tablename.equals(VersionFlowUtil.getFlowTableName(workflowidlist.get(6)))) {

                    //查询明细表2
                    RecordSet detail2 = ReimbursementDao.getDetail2(tablename, mainid);

                    List skr = new ArrayList();//收款人
                    while (detail2.next()){
                        skr.add(detail2.getStringNoTrim("skr"));
                    }
                    maps.put("skr",Joiner.on(",").join((Iterable<?>) skr));

                }
            }
        return maps;
    }




    //过滤报销流程
    public static RecordSet filter(String requestId,String startTime,String  endTime,String dw,int userid,List workflowidlist){
        RecordSet rs = new RecordSet();
        if (StrUtil.isNotEmpty(requestId)) {
            String workflowid = Joiner.on(",").join((Iterable<?>) workflowidlist);
            logger.info("workflowid："+workflowid);

            StringBuffer sb = new StringBuffer();
            sb.append("select se.requestid,ce.lastname,se.createdate,se.workflowid,se.requestname from  " +
                    "workflow_requestbase se,hrmResource ce " +
                    " where se.workflowid in(" + workflowid + ")  and se.creater = ce.id ");

            sb.append(" and se.requestid in(" + requestId + ") ");
            sb.append(" and se.creater ="+userid+" ");


//            //单位
//            if (StrUtil.isNotEmpty(dw)) {
//                sb.append(" and ce.subcompanyid1 =" + dw);
//            }

            //时间
            if (StrUtil.isNotEmpty(startTime)
                    && StrUtil.isNotEmpty(endTime)) {

                sb.append(" and se.createdate >='" + startTime +
                        "' and se.createdate<='" + endTime + "'");
            }

            sb.append(" order by se.requestid desc ");

            logger.info("待办sql打印："+ sb);

            rs.executeQuery(sb.toString());
        }
        return rs;
    }



}
